﻿Public Class oJournal

#Region "Create JE"
    Public Function CreateJE(ByVal NightAuditheader As DataRow) As String
        Dim cn As New Connection
        Dim dt1 As DataTable = New DataTable
        Dim errMessage As String = ""

        dt1 = cn.Integration_RunQuery(String.Format("select * from NightAuditRevenue where HeaderID = {0}", NightAuditheader("ID")))
        If dt1.Rows.Count > 0 Then
            errMessage = CreateJEForRevenue(NightAuditheader, dt1)
            If errMessage <> "" Then
                Return errMessage
            End If
        End If

        dt1 = cn.Integration_RunQuery(String.Format("select * from NightAuditPayment where HeaderID = {0}", NightAuditheader("ID")))
        If dt1.Rows.Count > 0 Then
            errMessage = CreateJEForPayment(NightAuditheader, dt1)
            If errMessage <> "" Then
                Return errMessage
            End If
        End If

        Return errMessage
    End Function
    Private Function CreateJEForPayment(ByVal header As DataRow, ByVal Detail As DataTable) As String
        Try
            Dim cn As New Connection
            Dim dtOJDT As DataTable = Functions.BuildTable("OJDT", "RefDate;DueDate;Memo")
            Dim dtJDT1 As DataTable = Functions.BuildTable("JDT1", "Account;Debit;Credit;OcrCode2;OcrCode3") ';OcrCode5")
            Dim dtGLMapping As DataTable = New DataTable("GLMapping")
            Dim DebitAccount As String = ""
            Dim CreditAccount As String = ""
            Dim ds As DataSet = New DataSet
            Dim DocType As String = "30"
            Dim ret As String = ""
            Dim xm As New oXML
            Dim query = ""

            dtOJDT.Rows.Add(CDate(header("RunDate")).ToString("yyyyMMdd"), CDate(header("RunDate")).ToString("yyyyMMdd"), "Night Audit Payment")
            For Each row As DataRow In Detail.Rows
                query = String.Format("select DebitAccount,CreditRevenueAccount from _GL_Mapping_Detail where replace(IsNull(PaymentMethod,''),' ','') = '{0}' ", Trim(row("PaymentMethod")))
                dtGLMapping = cn.SAP_RunQuery(query)

                If dtGLMapping.Rows.Count = 0 Then
                    Throw New Exception("Can not get GL Account")
                End If

                DebitAccount = dtGLMapping.Rows(0)("DebitAccount").ToString
                CreditAccount = dtGLMapping.Rows(0)("CreditRevenueAccount").ToString

                ValidateAccount(DebitAccount, CreditAccount)

                dtJDT1.Rows.Add(CreditAccount, 0, row("Amount"), row("Division"), row("Department")) ', row("TCode"))
                dtJDT1.Rows.Add(DebitAccount, row("Amount"), 0, row("Division"), row("Department")) ', row("TCode"))
            Next

            ds.Tables.Add(dtOJDT.Copy)
            ds.Tables.Add(dtJDT1.Copy)

            Dim sErrMsg As String
            sErrMsg = Functions.SystemInitial
            If sErrMsg <> "" Then
                Throw New Exception(sErrMsg)
            End If

            'Do somethings
            Dim xmlstr As String = xm.ToXMLStringFromDS(DocType, ds)
            ret = xm.CreateMarketingDocument("", xmlstr, DocType)

            If ret <> "" Then
                Throw New Exception(ret)
            End If

            Return ""
        Catch ex As Exception
            Return ex.Message
        End Try
        Return ""
    End Function
    Private Function CreateJEForRevenue(ByVal header As DataRow, ByVal Detail As DataTable) As String
        Try
            Dim cn As New Connection
            Dim dtGLMapping As DataTable = New DataTable("GLMapping")
            Dim dtOJDT As DataTable = Functions.BuildTable("OJDT", "RefDate;DueDate;Memo")
            Dim dtJDT1 As DataTable = Functions.BuildTable("JDT1", "Account;Debit;Credit;ProfitCode;OcrCode2;OcrCode3;OcrCode4") ';OcrCode5")
            Dim DebitAccount As String = ""
            Dim CreditRevAccount As String = ""
            Dim CreditTaxAccount As String = ""
            Dim CreditSCAccount As String = ""
            Dim ds As DataSet = New DataSet
            Dim DocType As String = "30"
            Dim ret As String = ""
            Dim xm As New oXML
            Dim query As String = ""
            Dim Amount As Double = 0
            dtOJDT.Rows.Add(CDate(header("RunDate")).ToString("yyyyMMdd"), CDate(header("RunDate")).ToString("yyyyMMdd"), "Night Audit Revenu")
            For Each row As DataRow In Detail.Rows
                query = String.Format("select DebitAccount,CreditRevenueAccount,CreditTaxAccount,CreditSCAccount from _GL_Mapping_Detail " +
                                      " where IsNull(MealPlan,'') = '{0}' and IsNull(Division,'') = '{1}' and IsNull(Department,'') = '{2}' and IsNull(MarketSegment,'') =  '{3}'and IsNull(TCode,'') = '{4}'",
                                      row("MealPlan"), row("Division"), row("Department"), row("MarketSegment"), row("TCode"))
                dtGLMapping = cn.SAP_RunQuery(query)

                If dtGLMapping.Rows.Count = 0 Then
                    Throw New Exception("Can not get GL Account")
                End If

                DebitAccount = dtGLMapping.Rows(0)("DebitAccount").ToString
                CreditRevAccount = dtGLMapping.Rows(0)("CreditRevenueAccount").ToString
                CreditTaxAccount = dtGLMapping.Rows(0)("CreditTaxAccount").ToString
                CreditSCAccount = dtGLMapping.Rows(0)("CreditSCAccount").ToString

                Double.TryParse(row("RevenueAmount").ToString, Amount)
                If Amount <> 0 Then
                    ValidateAccount(DebitAccount, CreditRevAccount)
                    dtJDT1.Rows.Add(CreditRevAccount, 0, Amount, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                    dtJDT1.Rows.Add(DebitAccount, Amount, 0, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                End If

                Amount = 0
                Double.TryParse(row("TaxAmount").ToString, Amount)
                If Amount <> 0 Then
                    ValidateAccount(DebitAccount, CreditTaxAccount)
                    dtJDT1.Rows.Add(CreditTaxAccount, 0, Amount, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                    dtJDT1.Rows.Add(DebitAccount, Amount, 0, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                End If

                Amount = 0
                Double.TryParse(row("SCAmount").ToString, Amount)
                If Amount <> 0 Then
                    dtJDT1.Rows.Add(CreditSCAccount, 0, Amount, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                    dtJDT1.Rows.Add(DebitAccount, Amount, 0, row("MealPlan"), row("Division"), row("Department"), row("MarketSegment")) ', row("TCode"))
                End If
               
            Next

            ds.Tables.Add(dtOJDT.Copy)
            ds.Tables.Add(dtJDT1.Copy)

            Dim sErrMsg As String
            sErrMsg = Functions.SystemInitial
            If sErrMsg <> "" Then
                Throw New Exception(sErrMsg)
            End If

            'Do somethings
            Dim xmlstr As String = xm.ToXMLStringFromDS(DocType, ds)
            ret = xm.CreateMarketingDocument("", xmlstr, DocType)

            If ret <> "" Then
                Throw New Exception(ret)
            End If

            Return ""
        Catch ex As Exception
            Return ex.Message
        End Try
    End Function
#End Region
    Public Sub ValidateAccount(ByVal ParamArray arrAcct() As String)

        Dim cn As Connection = New Connection
        Dim dt As DataTable = New DataTable
        Dim result As Boolean = False
        Dim accCode As String = ""
        If arrAcct.Length > 0 Then
            For i As Integer = 0 To arrAcct.Length - 1
                accCode = arrAcct(i)
                dt = cn.SAP_RunQuery(String.Format("select AcctName from OACT with(nolock) where AcctCode = '{0}'", accCode))
                result = dt.Rows.Count = 1
                If Not result Then
                    Exit For
                End If
            Next
        End If
        If result = False Then
            Throw New Exception(accCode + " is not valid")
        End If

    End Sub
End Class
